from app.utils.mysql_util import MySQLUtil
from app.utils.minio_util import MinioUtil
from datetime import datetime
import json

class MessageService:
    def __init__(self):
        self.db_util = MySQLUtil()
        self.minio_util = MinioUtil()

    def get_messages(self, sort_by='time', user_id=None, page=1, per_page=20):
        try:
            offset = (page - 1) * per_page
            order_by = 'm.created_time DESC' if sort_by == 'time' else 'm.like_count DESC'
            
            # Get messages with user info
            sql = f'''
                SELECT 
                    m.*,
                    u.username,
                    u.nickname,
                    u.avatar
                FROM message m
                JOIN user u ON m.user_id = u.id
                WHERE m.is_deleted = 0
                ORDER BY {order_by}
                LIMIT %s OFFSET %s
            '''
            
            messages = self.db_util.get_all(sql, (per_page, offset))
            if not messages:
                return []

            # Get comments for each message
            for message in messages:
                message['comments'] = self._get_message_comments(message['id'])
                
                # Handle images without presigned URLs
                if message['images']:
                    image_urls = message['images'].split(',')
                    message['images'] = [url.strip() for url in image_urls if url.strip()]
                else:
                    message['images'] = []
                
                # Handle avatar without presigned URL
                if message['avatar']:
                    message['avatar'] = message['avatar'].strip()
                
                message['created_time'] = message['created_time'].strftime('%Y-%m-%d %H:%M:%S')
                message['is_liked'] = False

                # Handle comment avatars without presigned URLs
                for comment in message['comments']:
                    if comment['avatar']:
                        comment['avatar'] = comment['avatar'].strip()
                    for reply in comment.get('replies', []):
                        if reply['avatar']:
                            reply['avatar'] = reply['avatar'].strip()

            return messages
        except Exception as e:
            print(f"Error getting messages: {str(e)}")
            raise

    def _get_message_comments(self, message_id):
        try:
            # Get top-level comments
            sql = '''
                SELECT 
                    c.*,
                    u.username,
                    u.nickname,
                    u.avatar
                FROM comment c
                JOIN user u ON c.user_id = u.id
                WHERE c.message_id = %s 
                AND c.parent_id IS NULL 
                AND c.is_deleted = 0
                ORDER BY c.created_time ASC
            '''
            comments = self.db_util.get_all(sql, (message_id,))
            
            # Get replies for each comment
            for comment in comments:
                comment['created_time'] = comment['created_time'].strftime('%Y-%m-%d %H:%M:%S')
                # Get replies
                sql = '''
                    SELECT 
                        c.*,
                        u.username,
                        u.nickname,
                        u.avatar,
                        pu.username as parent_username,
                        pu.nickname as parent_nickname
                    FROM comment c
                    JOIN user u ON c.user_id = u.id
                    JOIN comment pc ON c.parent_id = pc.id
                    JOIN user pu ON pc.user_id = pu.id
                    WHERE c.message_id = %s 
                    AND c.parent_id = %s 
                    AND c.is_deleted = 0
                    ORDER BY c.created_time ASC
                '''
                replies = self.db_util.get_all(sql, (message_id, comment['id']))
                for reply in replies:
                    reply['created_time'] = reply['created_time'].strftime('%Y-%m-%d %H:%M:%S')
                comment['replies'] = replies
            
            return comments
        except Exception as e:
            print(f"Error getting comments: {str(e)}")
            raise

    def create_message(self, user_id, content, images=None):
        try:
            # Handle image URLs
            image_urls = []
            if isinstance(images, list):
                image_urls = [url for url in images if url]
            elif isinstance(images, str):
                image_urls = [url for url in images.split(',') if url]

            # Insert message
            sql = '''
                INSERT INTO message (user_id, content, images)
                VALUES (%s, %s, %s)
            '''
            message_id = self.db_util.execute_insert(
                sql, 
                (user_id, content, ','.join(image_urls) if image_urls else None)
            )
            return message_id
        except Exception as e:
            print(f"Error creating message: {str(e)}")
            raise

    def create_comment(self, message_id, user_id, content, parent_id=None):
        try:
            # Start transaction
            self.db_util.begin_transaction()
            
            try:
                # Insert comment
                sql = '''
                    INSERT INTO comment (message_id, user_id, parent_id, content)
                    VALUES (%s, %s, %s, %s)
                '''
                comment_id = self.db_util.execute_insert(
                    sql, 
                    (message_id, user_id, parent_id, content)
                )

                # Update message comment count
                self.db_util.execute_update(
                    'UPDATE message SET comment_count = comment_count + 1 WHERE id = %s',
                    (message_id,)
                )
                
                # Commit transaction
                self.db_util.commit()
                return comment_id
            except Exception as e:
                # Rollback transaction
                self.db_util.rollback()
                raise
        except Exception as e:
            print(f"Error creating comment: {str(e)}")
            raise

    def get_message_by_id(self, message_id, user_id):
        try:
            # Get message details
            sql = '''
                SELECT m.*, u.username, u.nickname, u.avatar
                FROM message m
                JOIN user u ON m.user_id = u.id
                WHERE m.id = %s AND m.is_deleted = 0
            '''
            message = self.db_util.get_one(sql, (message_id,))
            
            if message:
                message['comments'] = self._get_message_comments(message_id)
                message['images'] = message['images'].split(',') if message['images'] else []
                message['created_time'] = message['created_time'].strftime('%Y-%m-%d %H:%M:%S')
                message['is_liked'] = False  # Since we don't track individual likes
            
            return message
        except Exception as e:
            print(f"Error getting message: {str(e)}")
            return None

    def toggle_message_like(self, message_id, user_id):
        try:
            # Start transaction
            self.db_util.begin_transaction()
            
            try:
                # Get current like status
                sql = 'SELECT like_count FROM message WHERE id = %s'
                result = self.db_util.get_one(sql, (message_id,))
                if not result:
                    raise Exception('消息不存在')
                
                # Update like count
                sql = 'UPDATE message SET like_count = like_count + 1 WHERE id = %s'
                self.db_util.execute_update(sql, (message_id,))
                
                # Get updated like count
                sql = 'SELECT like_count FROM message WHERE id = %s'
                result = self.db_util.get_one(sql, (message_id,))
                like_count = result['like_count'] if result else 0
                
                # Commit transaction
                self.db_util.commit()
                
                return {'is_liked': True, 'like_count': like_count}
            except Exception as e:
                # Rollback transaction
                self.db_util.rollback()
                raise
        except Exception as e:
            print(f"Error toggling message like: {str(e)}")
            raise